use serde::Deserialize;
use sqlx::{Executor, SqliteConnection};
use crate::{error::SqlxErr, store::get_pool};

pub mod curd;

pub const SQL_MCU_CREATE: &'static str = r#"
    CREATE TABLE IF NOT EXISTS tb_mcu (
        id INTEGER PRIMARY KEY AUTOINCREMENT, 
        name TEXT NOT NULL,
        family TEXT NOT NULL, 
        line TEXT NOT NULL, 
        package TEXT NOT NULL,
        UNIQUE (name)
    )
"#;


pub const SQL_IP_CREATE: &'static str = r#"
    CREATE TABLE IF NOT EXISTS tb_ip (
        id INTEGER PRIMARY KEY AUTOINCREMENT, 
        mcu_id INTEGER NOT NULL, 
        name TEXT NOT NULL, 
        instance_name NOT NULL
    )
"#;

async fn ip_insert(conn: &mut SqliteConnection, mcu_id: i64, ip: &Ip) -> Result<(), SqlxErr> {
    sqlx::query(r#"
        INSERT INTO tb_ip (
            mcu_id, name, instance_name
        ) VALUES (?, ?, ?)
    "#)
    .bind(mcu_id)
    .bind(&ip.name)
    .bind(&ip.instance_name)
    .execute(conn)
    .await?;
    Ok(())
}

pub const SQL_PIN_CREATE: &'static str = r#"
    CREATE TABLE IF NOT EXISTS tb_pin (
        id INTEGER PRIMARY KEY AUTOINCREMENT, 
        mcu_id INTEGER NOT NULL, 
        name TEXT NOT NULL, 
        position TEXT NOT NULL,
        type_name TEXT NOT NULL
    )
"#;

async fn pin_insert(conn: &mut SqliteConnection, mcu_id: i64, pin: &Pin) -> Result<(), SqlxErr> {

    let ret = sqlx::query(r#"
        INSERT INTO tb_pin (
            mcu_id, name, position, type_name
        ) VALUES (?, ?, ?, ?)
    "#)
    .bind(mcu_id)
    .bind(&pin.name)
    .bind(&pin.position)
    .bind(&pin.type_name)
    .execute(&mut *conn)
    .await?;
    
    let pin_id = ret.last_insert_rowid();
    if let Some(signals) = &pin.signals {
        for signal in signals.iter() {
            signal_insert(&mut *conn, pin_id, signal).await?;
        }
    }

    Ok(())
}

pub const SQL_SIGNAL_CREATE: &'static str = r#"
    CREATE TABLE IF NOT EXISTS tb_signal (
        id INTEGER PRIMARY KEY AUTOINCREMENT, 
        pin_id INTEGER NOT NULL, 
        name TEXT NOT NULL, 
        io_modes TEXT
    )
"#;

async fn signal_insert(conn: &mut SqliteConnection, pin_id: i64, signal: &Signal) -> Result<(), SqlxErr> {
    sqlx::query(r#"
        INSERT INTO tb_signal (
            pin_id, name, io_modes
        ) VALUES (?, ?, ?)
    "#)
    .bind(pin_id)
    .bind(&signal.name)
    .bind(&signal.io_modes)
    .execute(conn)
    .await?;
    Ok(())
}

pub async fn init() -> Result<(), SqlxErr> {
    let pool = get_pool();
    pool.execute(SQL_MCU_CREATE).await?;
    pool.execute(SQL_IP_CREATE).await?;
    pool.execute(SQL_PIN_CREATE).await?;
    pool.execute(SQL_SIGNAL_CREATE).await?;

    Ok(())
}

pub async fn insert_to_db(mcu: &Mcu) -> Result<(), SqlxErr> {

    let mut tx = get_pool().begin().await?;

    let ret = sqlx::query(r#"
        INSERT INTO tb_mcu (
            name, family, line, package
        ) VALUES (?, ?, ?, ?)
    "#)
    .bind(&mcu.name)
    .bind(&mcu.family)
    .bind(&mcu.line)
    .bind(&mcu.package)
    .execute(&mut *tx)
    .await?;

    let mcu_id = ret.last_insert_rowid();
    for ip in mcu.ips.iter() {
        ip_insert(&mut *tx, mcu_id, ip).await?;
    }
    for pin in mcu.pins.iter() {
        // if pin.type_name != "I/O" {
        //     continue;
        // }
        pin_insert(&mut *tx, mcu_id, pin).await?;
    }

    tx.commit().await?;

    Ok(())
}

#[derive(Debug, Deserialize)]
pub struct Mcu {
    #[serde(rename(deserialize = "RefName"))]
    pub name:String,

    #[serde(rename(deserialize = "Family"))]
    pub family: String,

    #[serde(rename(deserialize = "Line"))]
    pub line: String,

    #[serde(rename(deserialize = "Package"))]
    pub package: String,

    #[serde(rename(deserialize = "IP"))]
    pub ips: Vec<Ip>,

    #[serde(rename(deserialize = "Pin"))]
    pub pins: Vec<Pin>,
}

#[derive(Debug, Deserialize)]
pub struct Ip {
    #[serde(rename(deserialize = "Name"))]
    pub name: String,

    #[serde(rename(deserialize = "InstanceName"))]
    pub instance_name: String,
}

#[derive(Debug, Deserialize)]
pub struct Pin {
    #[serde(rename(deserialize = "Name"))]
    pub name: String,

    #[serde(rename(deserialize = "Position"))]
    pub position: String,

    #[serde(rename(deserialize = "Type"))]
    pub type_name: String,

    #[serde(rename(deserialize = "Signal"))]
    pub signals: Option<Vec<Signal>>,
}

#[derive(Debug, Deserialize)]
pub struct Signal {
    #[serde(rename(deserialize = "Name"))]
    pub name: String,

    #[serde(rename(deserialize = "IOModes"))]
    pub io_modes: Option<String>,
}




